1. The academic office wants to identify students whose CGPA falls between 6 and 8 (inclusive) for a special improvement program. Write a query to find the USN, first name, and CGPA of these students.
SELECT Usn,First_name,CGPA FROM Students WHERE CGPA BETWEEN 6 AND 8
2. List College names with Unknown Date of Build.
SELECT College FROM Colleges WHERE Date_of_build IS NULL
3. Due to economic conditions, certain companies have decided to decrease the package of students by 20%. Write an SQL query to show the Student's USN, Current Package, and Decreased_Package of the students as 'Decreased_Package' accordingly.
SELECT Usn,package,package * 0.80 AS Decreased_Package FROM Students
4. The placement cell wants to identify companies that offer packages between 300,000 and 800,000 for mid-level roles. Write a query to list the names of these companies.
SELECT Company_name FROM Companies WHERE Package BETWEEN 300000 AND 800000
5. A College is reviewing some data and wants to display the full names of lecturers along with their designations to ensure proper identification separated by '-' displayed in a field named 'Name_Designation'.
SELECT CONCAT(First_name,'-', Designation) AS Name_Designation FROM Lecturers;
6. List College names with Known Date of Build.
SELECT College FROM Colleges WHERE Date_of_build IS NOT NULL;
7. In a university, administrators are curious about the lengths of branch names across different colleges to ensure consistency in database entry. They want to see the branch names and their respective lengths in a field named 'Branch_Name_Length'.
SELECT Branch_name, LENGTH(Branch_name) AS Branch_Name_Length FROM Branches;
8. The college wants to identify students who are eligible for the Dean's List, which requires a CGPA between 8 and 9 (inclusive). Write a query to find the USN and first name of these students.
SELECT Usn,First_name FROM Students WHERE CGPA BETWEEN 8 AND 9;
9. Assume you are tasked with enhancing student's motivation by rewarding a hike of 15% to their Current Package. Write a SQL query to show the Student's USN, Current Package, and the Increased Package field named as 'Increased_Package' of the students accordingly.
SELECT Usn,package,package * 1.15 AS Increased_Package FROM Students;
10. The academic committee is reviewing subjects taught in semester 5 or 6 for a curriculum update. Write a query to retrieve the subject names and hours for these subjects using the IN operator.
SELECT Subject_name,Hours FROM Subjects WHERE Semester IN (5,6)
11. The academic committee needs to find the student's USN and Test 2 Marks between 15 and 20. Write a query to retrieve the Usn, Subject ID (SID), and Test2 marks.
SELECT Usn,SID,Test2 FROM Marks WHERE Test2 BETWEEN 15 AND 20
12. The placement cell wants to invite companies that offer packages of 400,000 or 1,200,000 for the upcoming recruitment drive. Write a query to list the names of these companies using IN operator.
SELECT Company_name FROM Companies WHERE Package IN (400000,1200000)
13. A researcher is studying student names and their lengths across different departments. They want to find the names of students and their lengths as Name_Length for those whose names are longer than 5 characters.
SELECT First_name, LEN(First_name) AS Name_length FROM Students WHERE LEN(First_name) > 5
14. The administration wants to identify lecturers who have designations indicating a medium level of experience (e.g., Assistant Professor to Associate Professor). Write a query to list the first names and designations of these lecturers.
SELECT First_name,Designation FROM Lecturers WHERE Designation IN('Assistant Professor', 'Associate Professor')
15. As an educational analyst, you are tasked with calculating the total percentage marks obtained by each student based on their performance in Test1, Test2, and Test3. Each test has a maximum possible score of 30. Write a SQL query to compute and display the USN, Subject ID (SID), Test1, Test2, Test3, and total percentage marks as a field named 'Total_Percentage' for each student.
SELECT USN,SID, Test1,Test2,Test3,((Test1 + Test2 + Test3) / 90.0) * 100 AS Total_Percentage FROM Marks;